"""Illustrates the asyncio engine / connection interface.

In this example, we have an async engine created by
:func:`_engine.create_async_engine`.   We then use it using await
within a coroutine.

"""

import asyncio

from sqlalchemy import URL, Column, select
from sqlalchemy import Integer
from sqlalchemy import MetaData
from sqlalchemy import String
from sqlalchemy import Table
from sqlalchemy.ext.asyncio import create_async_engine
from sqlalchemy.ext.asyncio import async_sessionmaker
from entity import Book
from sqlalchemy.ext.asyncio import AsyncSession

meta = MetaData()

t1 = Table("t1", meta, Column("id", Integer, primary_key=True), Column("name", String))


async def insert_objects(async_session: async_sessionmaker[AsyncSession]) -> None:
    async with async_session.begin() as session:
        session.add_all(
            [
                Book(book_name="Python 101", price=100.00),
                Book(book_name="java 101", price=100.00),
            ]
        )


async def async_main():
    # engine is an instance of AsyncEngine
    engine = create_async_engine(
        url=URL.create(
            "mysql+aiomysql",
            username="root",
            password="sql123",
            host="113.44.65.140",
            port=3306,
            database="flex-study",
            query={"charset": "utf8mb4"},
        ),
        echo=True,
        pool_size=20,  # 设置连接池的大小
        max_overflow=25,  # 设置最大溢出连接数
        pool_recycle=60 * 3,  # 设置连接回收时间（秒）
        pool_timeout=10,  # 设置连接超时时间（秒）
    )

    # conn is an instance of AsyncConnection
    async_session = async_sessionmaker(engine, expire_on_commit=False)
    await insert_objects(async_session)
    async with async_session.begin() as session:
        stmt = select(Book).add_columns(Book.book_name)
        res = await session.execute(stmt)
        for row in res:
            print(row)

    await engine.dispose()


asyncio.run(async_main())
